﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using DistributeTransaction.Models;

namespace DistributeTransaction.DAL
{
    public class OrderRepository : GenericRepository<Order>
    {
        public OrderRepository(DatabaseContext context)
            : base(context)
        {
        }

        public Order GetOrderByOrderId(int orderId)
        {
            const string query = @"SELECT 
[OrderID],
[OrderDate],
[ShippedDate], 
[ShipName], 
[ShipAddress],
[ShipCity],
[ShipPostalCode],
[ShipCountry],
[Freight],
[Status]
FROM [dbo].[Order] 
WHERE [OrderID] = @p0";
            return GetWithRawSql(query, orderId).FirstOrDefault();
        }

        public IEnumerable<Order> GetOrders(Guid userId)
        {
            const string query = @"SELECT 
[OrderID],
[OrderDate],
[ShippedDate], 
[ShipName], 
[ShipAddress],
[ShipCity],
[ShipPostalCode],
[ShipCountry],
[Freight],
[Status]
FROM [dbo].[Order] 
WHERE UserId = @p0";
            return GetWithRawSql(query, userId).ToList();
        }

        public Order GetRecentlyOrder(Guid userId)
        {
            const string sql = @"SELECT TOP 1
[OrderID]
,[OrderDate]
,[ShippedDate]
,[ShipName]
,[ShipAddress]
,[ShipCity]
,[ShipRegion]
,[ShipPostalCode]
,[ShipCountry]
,[Freight]
,[Status]
FROM [dbo].[Order] O
WHERE O.[UserId] = @p0
ORDER BY O.[OrderDate] DESC";

            Order item = GetWithRawSql(sql, userId).FirstOrDefault();
            return item == null ? new Order() : item;
        }

        public IEnumerable<OrderDetail> GetOrderByID(int orderId)
        {
            const string query = @"SELECT 
O.[OrderID]
,O.[ProductID]
,O.[UnitPrice]
,O.[Quantity]
,O.[Coupon]
,P.ProductName
,R.[Status]
FROM [dbo].[OrderDetail] O
JOIN dbo.[Order] R ON O.[OrderID] = R.[OrderID]
JOIN dbo.Product P ON O.[ProductID] = P.[ProductID] 
WHERE O.[OrderID] = @p0";
            return dbContext.Set<OrderDetail>().SqlQuery(query, orderId).ToList();
        }


        public OrderSummary GetOrderSummaryByID(int orderId)
        {
            PaymentRepository paymentRespository = new PaymentRepository(base.dbContext);

            OrderSummary order = new OrderSummary();
            order.OrderID = orderId;
            order.details = GetOrderByID(orderId);
            order.Payments = paymentRespository.GetOrderPayments(orderId);
            
            var o = GetOrderByOrderId(orderId);
            order.ShipName = o.ShipName;
            order.ShipAddress = o.ShipAddress;
            order.ShippedDate = o.ShippedDate;
            order.ShipCity = o.ShipCity;
            order.ShipCountry = o.ShipCountry;
            order.ShipPostalCode = o.ShipPostalCode;
            
            return order;
        }


        public Order GetOrderPayments(decimal amount, Guid userId)
        {
            Order order = new Order();
            if (order.Payments == null)
            {
                PaymentRepository paymentRespository = new PaymentRepository(base.dbContext);
                order.Payments = paymentRespository.GetTempPaymentForShoppingCart(userId);
            }

            amount -= order.Payments.Sum(item => item.Amount);

            if (order.Current == null)
            {
                order.Current = new Payment(amount);
            }
            else
            {
                order.Current.Amount = amount;
            }

            if (order.Cards == null)
            {
                order.Cards = new Dictionary<int, string>();
                CardRepository cardRepository = new CardRepository(base.dbContext);
                foreach (CardInfo item in cardRepository.GetCardInfos(userId))
                {
                    order.Cards.Add(item.AccountId, item.CardNumber);
                }
            }

            return order;
        }

        internal bool CheckOut(Order order, Guid userId)
        {
            const string sql = @"dbo.Insert_Order";

            SqlParameter pUserId = new SqlParameter();
            pUserId.ParameterName = "UserId";
            pUserId.Direction = ParameterDirection.Input;
            pUserId.DbType = DbType.Guid;
            pUserId.Value = userId;

            SqlParameter pShipName = new SqlParameter();
            pShipName.ParameterName = "ShipName";
            pShipName.Direction = ParameterDirection.Input;
            pShipName.DbType = DbType.String;
            pShipName.Value = order.ShipName;

            SqlParameter pShipAddress = new SqlParameter();
            pShipAddress.ParameterName = "ShipAddress";
            pShipAddress.Direction = ParameterDirection.Input;
            pShipAddress.DbType = DbType.String;
            pShipAddress.Value = order.ShipAddress;

            SqlParameter pShipCity = new SqlParameter();
            pShipCity.ParameterName = "ShipCity";
            pShipCity.Direction = ParameterDirection.Input;
            pShipCity.DbType = DbType.String;
            pShipCity.Value = order.ShipCity;

            SqlParameter pShipCountry = new SqlParameter();
            pShipCountry.ParameterName = "ShipCountry";
            pShipCountry.Direction = ParameterDirection.Input;
            pShipCountry.DbType = DbType.String;
            pShipCountry.Value = order.ShipCountry;

            SqlParameter pShipPostalCode = new SqlParameter();
            pShipPostalCode.ParameterName = "ShipPostalCode";
            pShipPostalCode.Direction = ParameterDirection.Input;
            pShipPostalCode.DbType = DbType.String;
            pShipPostalCode.Value = order.ShipPostalCode;

            SqlParameter pOrderId = new SqlParameter();
            pOrderId.ParameterName = "OrderId";
            pOrderId.Direction = ParameterDirection.Output;
            pOrderId.DbType = DbType.Int32;

            ExecuteStoreProcedure(sql,
                pUserId,
                pShipName,
                pShipAddress,
                pShipCity,
                pShipPostalCode,
                pShipCountry,
                pOrderId);

            return pOrderId.Value is int && ((int)pOrderId.Value > 0);
        }

        internal void CancelOrder(int orderId)
        {
            const string sql = @"dbo.Cancel_Order";

            SqlParameter pOrderId = new SqlParameter();
            pOrderId.ParameterName = "OrderId";
            pOrderId.Direction = ParameterDirection.Input;
            pOrderId.DbType = DbType.Int32;
            pOrderId.Value = orderId;

            ExecuteStoreProcedure(sql, pOrderId);
        }
    }
}